"""
案例脚本

创建handler
>>> handler = Handler()

获取各部门各人员
>>> department_artist = handler.get_display_department_artists()

获取显示的所有部门列表
>>> display_departments=handler.display_departments()

获取各部门人员等级信息
>>> department_artists_level_map=handler.get_department_artists_level_map()
{'01美术':{'吴子昱':'Px',...}}

指定时间段内的所有任务
>>> all_department_per_project_map=handler.get_all_department_per_project_map('2020-12-10','2020-12-12')

获取指定时间段的延期任务,传入所有任务不需要再次查询
>>> department_overtime_task_map=handler.get_all_department_overtime_task('2020-12-10','2020-12-12')

获取最原始的查询结果
>>> handler.test_get_ori_all_data()

"""

import pprint
import xlsxwriter
from TD.DBhandler import Handler
from TD.CGTWDB6 import CGTW6Handler
from TD.CreateDB import DBManager
from TD.sync5to6 import Sync5to6
from TD.ScheduleDB import SchedulerHandler
from TD.ScheduleDB5 import Scheduler5Handler
from TD.mz_date import MZ_Date
from TD.CentralDB import CSDBManager

"""
日期计算
"""
# mz = MZ_Date()
# start_date = '2021-08-02'
# end_date = '2021-08-03'
#
# date_length = mz.delta_date(start_date,end_date)+1
# print(date_length)
#


"""
-------------------------------------------------------------------------------
综合数据库操作
-------------------------------------------------------------------------------
"""


handler = Handler()
# 获取管理者信息
# result = handler.get_manager_info()
# print(result)

# 一次性同步中央数据库中的taskhistory表里的module
# handler.sync_central_task_module()


# handler.sync_r_project()
# handler.exportDesription(r'F:\PycharmOut\DataSets\description')
# # # 获取项目映射
# project_map = handler.scheduler_handlder.get_project_map()
# print(project_map)

# 人名设置为空
# handler.update_artist(['proj_shbl,shot,C5F216BC-19C8-5F47-3CEB-71AF5B0B5842'])

# #获取部门信息
# department_info, department_display_info, departments = handler.get_department_info()
# print(department_display_info)  #显示的部门的信息

# #获取部门人员信息
# department_artist = handler.get_display_department_artists()  #获取各部门各人员
# print(department_artist)

# #获取显示的所有部门列表
# display_departments=handler.display_departments()
# print(display_departments)

# #获取各部门人员等级信息 {'01美术':{'吴子昱':'Px',...}}
# department_artists_level_map=handler.get_department_artists_level_map()  #获取各部门人员等级信息 {'01美术':{'吴子昱':'Px',...}}
# print(department_artists_level_map)

# #指定时间段内的所有任务

# all_department_per_project_map=handler.get_all_department_per_project_map('2021-08-02','2021-08-07')
# # print(all_department_per_project_map)
# print(type(all_department_per_project_map))
# print(all_department_per_project_map.keys())
# print(type(all_department_per_project_map['08合成一']))
# tasks = []
# for i in all_department_per_project_map['08合成一'].values():
#     for data in i:
#         if data['artist'] == '沈诣洋':
#             tasks.append(i)
#             print(tasks)
# print(len(tasks))

# result = handler.get_tasks_in_dates('2021-08-02','2021-08-07',account_id='9AFE9D99-3B4C-2045-008B-1FC27FE1D75B')
# print(len(result))
# for i in result:
#     print(i['show_name'],i['start_date'],i['end_date'])
#
# for i in result:
#     if i['artist'] == '沈诣洋':
#       tasks.append(i)
# print(len(tasks))




# #获取指定时间段的延期任务,传入所有任务不需要再次查询
# department_overtime_task_map=handler.get_all_department_overtime_task('2020-12-10','2020-12-12')
# print(department_overtime_task_map)

# #获取指定时间段的通过任务,传入所有任务不需要再次查询
# all_department_approve_task =handler.get_all_department_approve_task('2021-08-02','2021-08-07')
# print(all_department_approve_task)

# #获取最原始的查询结果
# handler.test_get_ori_all_data()
# columns=handler.get_table_colums('projects','timeaxis')
# print(columns)

# 数据转pd,暂时没有
# df=handler.table2pd('projects','timeaxis')
# df=handler.table2pd('projects',columns_names = ['id','project_name','database'],schema='timeaxis')
# print(df.head())

# 根据传入schema名称获取其所有任务
# r_tasks = handler.get_cgtw_tasks('proj_pltest')
# print(len(r_tasks))
# for i in r_tasks:
#     print(i)

# # 同步r_project
# handler.sync_r_project()

# # 同步r_account
# handler.sync_r_account()

# # 给定任务id 同步r_tasks 用于cgtw手动同步
# handler.sync_r_tasks('proj_pltest',['C9BE6333-4E81-1B2A-B9E3-B7E3A84CB5DF'])
# handler.sync_r_tasks('proj_xl')
# handler.sync_r_tasks('proj_xl',['D9C8502F-CA8F-6CF6-078A-D8D5A0C04BCB'])
# #同步r_department
# handler.sync_r_department()
# active_database_project_info = handler.cgtw_handler.active_database_project_info

#删除重复的任务
# handler.clear_repeat_rtask(active_database_project_info.keys())
# handler.clear_repeat_rtask(['proj_pltest'])

# 同步r_task


# 同步状态
handler.sync_tasks(['proj_xl'])#,cg_id_list=['proj_xl,shot,D9C8502F-CA8F-6CF6-078A-D8D5A0C04BCB'])
# handler.sync_tasks(['proj_zyjfq'])

#同步所有库
#
# handler.sync_all()


# active_database_project_info = handler.cgtw_handler.active_database_project_info
# active_databases = active_database_project_info.keys()
# for proj in active_databases:
#     print("开始同步项目:",proj)
#     handler.sync_r_tasks(proj)
#     print("同步项目完成:",proj)
# proj = 'proj_gjwd'
# print("开始同步项目:",proj)

# handler.sync_r_tasks(proj)
# print("同步项目完成:",proj)
#更新token
# handler.update_token()




"""
-------------------------------------------------------------------------------
cgtw 6数据库操作
-------------------------------------------------------------------------------
"""

# cg_hander = CGTW6Handler()

# 默认项目信息
# cg_hander.database_project_info
# print(cg_hander.active_database_project_info)

# 主动获取激活项目database与full_name
# result = cg_hander._get_active_project()
# print(result)

# #获取module中指定的列中的所有类型
# cg_hander._test_get_column_types('proj_by.entity','link_id')

# #获取部门信息
# department_id_info,id_department_info = cg_hander.get_department_info()
# print(department_id_info)
# print(id_department_info)

# #获取阶段信息
# pipeline_id_info,id_pipeline_info = cg_hander.get_pipeline_info()
# print(pipeline_id_info,id_pipeline_info)

#
# cg_hander._test_get_column_types('public.entity','module')
# database_project_info=cg_hander.get_project_info()  #获取项目信息
# print(database_project_info)
# department_id_account_info=cg_hander.get_account_info()
# print(len(department_id_account_info),department_id_account_info)


# cg_hander._test_get_column_types('proj_pltest.entity','module')
# cg_hander._test_get_column_types('proj_pltest.entity','module_type')

# cgtasks = cg_hander.get_cgtw_task_info('proj_zczl',task_id_list=['1798BCEC-A709-FE00-0B46-615A473E6119'])
# print(cgtasks)
#
# print(len(cgtasks))
# for i in cgtasks:
#     print(i)

# 获取project 信息

# project_info = cg_hander._get_project_info()
# print(project_info)


"""
-------------------------------------------------------------------------------
排期软件数据库、数据表创建，删除
-------------------------------------------------------------------------------
"""

# manager = DBManager('testforcgtw6')
# manager = DBManager('scheduler')
# manager.create_r_task_table()
# manager._init_scheduler_tables()

# manager.create_db('forcgtw6')  #创建database
# manager.get_all_database()  #获取所有的database名称
# manager.select_schema('postgres')

# result =manager.creat_schema('timeaxis') #创建schema
# print(result)

# result =manager.create_account_table() #创建account,默认是在timeaxis schema下
# print(result)

# result =manager.create_r_account_table()
# print(result)

# result =manager.create_public_conf_table()
# print(result)

# result =manager.create_projects_table()
# print(result)
#
# result =manager.create_nodes_table()
# print(result)

# result =manager.create_r_project_table()
# print(result)

# result =manager.create_r_department_table()
# print(result)

# result =manager.create_assigned_task_table()
# print(result)

# result =manager.create_delete_task_table()
# print(result)

# result =manager.create_custom_task_table()
# print(result)
#
# result =manager.create_group_task_table()
# print(result)

# result =manager.create_task_table()
# print(result)

# result =manager.create_r_task_table()
# print(result)



"""
-------------------------------------------------------------------------------
数据迁移5 - > 6
-------------------------------------------------------------------------------
"""
# sync = Sync5to6('postgres','testforcgtw6')
# sync = Sync5to6('postgres','scheduler')
# active_database = sync.get_active_databases()
# print(active_database)
# sync.sync_account_table()
# sync.sync_custom_task_table()
# sync.sync_nodes_table()
# sync.sync_projects_table()
# sync.reindex_project_id()

# sync.sync_public_conf_table()
# sync.sync_r_account_table()
# sync.sync_r_department_table()
# sync.sync_r_project_table()
# sync.sync_task_table(active_database=True)
# sync.sync_r_task_table(active_database=True)
# sync.sync_group_task_table(active_database=True)



"""
-------------------------------------------------------------------------------
ScheduleDB 操作
-------------------------------------------------------------------------------
"""
# shandler = SchedulerHandler()
# shandler.public_conf_info()
# print(shandler.get_project_eps_list_map())
# print(shandler.get_active_projects_info())
# print(shandler.get_public_conf_info())
# print(shandler.get_current_account_info('ceshi2','zz123456'))
# shandler.filter_r_task_info(['proj_by'],['07特效一'])
# cgtasks=shandler.cgtwtask_in_departments_dates(['07特效一'],'2020-12-01','2020-12-31')
# print(len(cgtasks))
# customtasks=shandler.customtask_in_departments_dates(['07特效一'],'2020-12-01','2020-12-31')
# print(len(customtasks))

# 获取任务
# result = shandler.task_in_dates('2021-11-01','2021-11-01')
# print(result)
# grouptasks=shandler.grouptask_in_departments_dates(['08合成一'],'2020-12-01','2020-12-31')
# print(len(grouptasks))
# print(grouptasks.get('祝阳',{}))
# for v in grouptasks:
#     print(v)

# 获取人员信息
# result = shandler.r_account_artist_map_in_display_departments()  # account_id : department
# print(result)

# 获取描述
# def export_description(dir_path):
#     import os
#     import pandas as pd
#
#
# result = shandler.getDescriptionByDatabase('proj_fndh')
# print(len(result),result[0])
#
# df = pd.DataFrame(result)


"""
-------------------------------------------------------------------------------
ScheduleDB5 操作
-------------------------------------------------------------------------------
"""
# shandler5 =Scheduler5Handler()
# data=shandler5.get_data()
# path = r'E:\Temp\data.xlsx'
# workbook=xlsxwriter.Workbook(path)
# worksheet=workbook.add_worksheet('sheet1')
# headings=['项目名称','任务名称','起始日期','结束日期','帧数','显示工时','人名','部门','备注','任务类型']
# worksheet.write_row(0,0,headings)
# for i in range(len(data)):
#     worksheet.write_row(i+1,0,data[i])
# workbook.close()

# tasks=shandler5.cgtw_task_in_dates('2021-03-03','2021-03-03')
# print(len(tasks))
# mod_tasks =[]
# for data in tasks:
#     if data['department'] =='02模型':
#         mod_tasks.append(data)
#         print(data)
#
# print('数量:',len(mod_tasks))

# account_id_artist_map = shandler5.r_account_display_departments()
# print(len(account_id_artist_map))
# artist_account_id_map = {}
# for account_id,artist in account_id_artist_map.items():
#     artist_account_id_map[artist] = account_id
# pprint.pprint(artist_account_id_map)
# print(artist_account_id_map)
# print(len(artist_account_id_map))


"""
-------------------------------------------------------------------------------
CentralDB 操作
-------------------------------------------------------------------------------
"""

# cshandler = CSDBManager('postproduction')
# cshandler.clearTableAndResetId('timeaxis.venderhistory')
# cshandler.create_schema('timeaxis')
# cshandler.create_projectHistory_table()
# result = cshandler.create_taskhistory_table()
# result = cshandler.create_vender_history_table()
# print(result)
data = {'database': 'proj_my', 'task_id': 'xxxxxxx', 'info_id': 'xxxxx', 'status': 'Aprove', 'account_id': 'xxxxx',
        'artist': 'xxxx', 'start_date': '2021-11-11', 'end_date': '2021-11-11',
        'first_submit_time': '2021-10-27 00:00:00',
        'work_hour': '2.5', 'shot_final_status': 'Wait', 'frame': '56'}
# cshandler.save_taskHistory([data])
# result = cshandler.clearTableAndResetId('timeaxis.taskhistory')
# print(result)
weeks = [['2021-09-20', '2021-09-21', '2021-09-22', '2021-09-23', '2021-09-24', '2021-09-25', '2021-09-26'],
         ['2021-09-27', '2021-09-28', '2021-09-29', '2021-09-30', '2021-10-01', '2021-10-02', '2021-10-03'],
         ['2021-10-04', '2021-10-05', '2021-10-06', '2021-10-07', '2021-10-08', '2021-10-09', '2021-10-10'],
         ['2021-10-11', '2021-10-12', '2021-10-13', '2021-10-14', '2021-10-15', '2021-10-16', '2021-10-17'],
         ['2021-10-18', '2021-10-19', '2021-10-20', '2021-10-21', '2021-10-22', '2021-10-23', '2021-10-24'],
         ['2021-10-25', '2021-10-26', '2021-10-27', '2021-10-28', '2021-10-29', '2021-10-30', '2021-10-31'],
         ['2021-11-01', '2021-11-02', '2021-11-03', '2021-11-04', '2021-11-05', '2021-11-06', '2021-11-07'],
         ['2021-11-08', '2021-11-09', '2021-11-10', '2021-11-11', '2021-11-12', '2021-11-13', '2021-11-14']]
# result = cshandler.get_taskHistory_by_weeks(weeks)
# result = cshandler.get_taskHistory_by_start_end_date('2021-11-09','2021-11-14')
# print(len(result))
# for i in result:
#     print('start_date:',i['start_date'],'end_date:',i['end_date'],)


# indays = cshandler._intersection_days('2021-11-12','2021-11-17','2021-11-15','2021-11-20')
# print(indays)
# result = cshandler.get_projectHistory('proj_pltest2','eps01','02_01','本部')
# result = cshandler.get_projectHistory('proj_pltest')
# print(result)
# data = {}
# data['date'] = '2020-10-20'
# data['allMinutes'] = 120
# data['allCounts'] = 2
# data['leftMinutes'] = 33
# data['leftCounts'] = 4
# data['database'] = 'proj_pltest2'
# data['projectName'] = '项目测试'
#
# cshandler.save_projectHistory(data)
# cshandler.update_taskstatus('2022-11-27')











